﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using System.Collections;

namespace data_adapter
{
    //表格方向，横向和竖向
    enum item_type
    {
        room_item = 0,
        cubicle_item =1,
        bay_item = 2,
        device_item = 3,
        board_item = 4,
        port_item = 5,
        vterminal_item = 6,
        vterminal_connection_item = 7,
        room_config_item = 8,
        cubicle_config_item = 9,
        bay_config_item = 10,
        port_connection_item  = 11,
        infoset_item = 12,
        infoset_folder_item = 13,
        cable_item = 14,
        cable_config_item = 15,
        vlan_item = 16,
        vlan_config_item = 17,
        volume_item = 18,
        dwg_item = 19
    }


    //小室信息，从room表获取
    public struct room_item
    {
        public int room_id;
        public string name;
    };

    //屏柜信息，从cubicle表获取
    public struct cubicle_item
    {
        public int cubicle_id;
        public string name;
        public string size;
        public string manufacture;
    };

    //间隔信息，从bay表获取
    public struct bay_item
    {
        public int bay_id;
        public string name;
        public string number;
        public int vlevel;
    };

    //设备信息，从device表获取
    public struct device_item
    {
        public int device_id;
        public byte device_type;
        public string name;
        public string description;
        public string manufacture;
        public string type;
        public string config_version;
        public int vlevel;
        public string category;
        public byte[] content;
        public bool   b_lock;
    };

    //板卡插件信息，从board表获取
    public struct board_item
    {
        public int board_id;
        public int device_id;
        public string name;
        public int position;
        public string description;
        public string manufacture;
        public string type;
    };

    //端口信息，从port表获取
    public struct port_item
    {
        public int port_id;
        public int board_id;
        public string name;
        public int group;
        public byte type;
        public byte direction;
        public byte fiber_plug;
        public byte fiber_mode;
        public bool cascade;
    };

    //虚端子信息，从vterminal表获取
    public struct vterminal_item
    {
        public int vterminal_id;
        public int device_id;
        public byte type;
        public byte direction;
        public int vterminal_no;
        public string description;
        public string pro_desc;
        public byte[] ap_name;
        public string ld_inst;
        public string ln_prefix;
        public string ln_class;
        public string ln_inst;
        public string do_name;
        public string da_name;
    };

    //虚端子连接信息，从vterminal_connection表获取
    public struct vterminal_connection_item
    {
        public int connection_id;
        public int txvterminal_id;
        public int rxvterminal_id;
    };

    //小室配置信息，从room_config表获取
    public struct room_config_item
    {
        public int config_id;
        public int room_id;
        public int cubicle_id;
    };

    //屏柜配置信息，从cubicle_config表获取
    public struct cubicle_config_item
    {
        public int config_id;
        public int cubicle_id;
        public int device_id;
        public int position;
        public String number;
    };

    //间隔配置信息，从bay_config表获取
    public struct bay_config_item
    {
        public int config_id;
        public int bay_id;
        public int device_id;
    };

    //端口连接信息，从port_connection表获取
    public struct port_connection_item
    {
        public int connection_id;
        public int infoset_id;
        public int txport_id;
        public int rxport_id;
        public byte cable_type;
    };

    //信息集信息，从infoset表获取
    public struct infoset_item
    {
        public int infoset_id;
        public string name;
        public string description;
        public byte type;
        public int txied_id;
        public int txport_id;
        public int rxied_id;
        public int rxport_id;
        public int switch1_id;
        public int switch2_id;
        public int switch3_id;
        public int txodf_id;
        public int rxodf_id;
    };

    //信息集目录信息，从infoset_folder表获取
    public struct infoset_folder_item
    {
        public int folder_id;
        public string name;
        public int infoset_id;
    };

    //光缆信息，从cable表获取
    public struct cable_item
    {
        public int cable_id;
        public int cubicle1_id;
        public int cubicle2_id;
        public string name;
        public byte cable_type;
        public string description;
        public string type;
        public double length;
    };

    //光缆配置信息，从cable_config表获取
    public struct cable_config_item
    {
        public int config_id;
        public int cable_id;
        public int connection_id;
        public int index;
        public string color;
        public string pipe_color;
        public bool b_lock;
    };

    //VLAN信息，从vlan表获取
    public struct vlan_item
    {
        public int vlan_id;
        public string name;
        public string description;
    };

    //VLAN分配信息，从vlan_config表获取
    public struct vlan_config_item
    {
        public int config_id;
        public int port_id;
    };

    //卷册目录信息，从volume表获取
    public struct volume_item
    {
        public int volume_id;
        public string name;
        public string description;
    };

    //图纸信息，从dwg表获取
    public struct dwg_item
    {
        public uint dwg_id;
        public uint volume_id;
        public string name;
        public string description;
        public string stage;
        public DateTime? datetime;
        public sbyte? type;
        public byte[] file;
    };


    class DataAdapter
    {
        private MySqlConnection conn;
        private String[] tableNames = new String[20] 
            {"room","cubicle","bay","device","board","port","vterminal","vterminal_connection","room_config","cubicle_config", 
             "bay_config","port_connection","infoset","infoset_folder","cable","cable_config","vlan","vlan_config","volumn","dwg" };
        private String[] idNames = new String[20] 
            {"room_id","cubicle_id","bay_id","device_id","board_id","port_id","vterminal_id","connection_id","config_id","config_id", 
             "config_id","connection_id","infoset_id","folder_id","cable_id","config_id","vlan_id","config_id","volume_id","dwg_id" }; 

        public DataAdapter() {
            this.InitConnection();
        }

        public bool InitConnection()
        {
            try
            {
                conn = new MySqlConnection("Database=dctss_base;Data Source=localhost;User Id=root;Password=zwxx");
                conn.Open();
                MySqlCommand command = new MySqlCommand("set names gb2312", conn);
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("An error occurred: '{0}'", e);
                conn.Close();
                return false;
            }

            return true;
        }


        public void CloseConnection()
        {
            if (conn.State == ConnectionState.Open)
            {
                Console.WriteLine("关闭数据库连接");
                conn.Close();
            }
        }

        private String GetString(Object obj)
        {
            if (obj == DBNull.Value)
                return null;
            else
                return (String)obj;
        }

        private sbyte? GetSByte(Object obj)
        {
            if (obj == DBNull.Value)
                return null;
            else
                return (sbyte)obj;
        }

        private DateTime? GetDateTime(Object obj)
        {
            if (obj == DBNull.Value)
                return null;
            else
                return (DateTime)obj;
        }

        private String GetString(MySqlDataReader reader, int i)
        {
            if (reader.IsDBNull(i))
                return null;
            else
                return reader.GetString(i);
        }

        private int? GetInt16(MySqlDataReader reader, int i)
        {
            if (reader.IsDBNull(i))
                return null;
            else
                return reader.GetInt16(i);
        }

        private double? GetDouble(MySqlDataReader reader, int i)
        {
            if (reader.IsDBNull(i))
                return null;
            else
                return reader.GetDouble(i);
        }

        private String getArrayString(int[] array)
        {
            StringBuilder result = new StringBuilder();
            for (int i = 0; i < array.Length; i++)
            {
                result.Append(array[i].ToString());
                if (i < array.Length - 1)
                    result.Append(',');
            }
            return result.ToString();

        }

        public DataSet GetDataSet(string sql)
        {
            DataSet dataset = new DataSet();

            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            adapter.SelectCommand = new MySqlCommand(sql, conn);
            adapter.Fill(dataset);

            return dataset;
        }

        public DataTable GetDataTable(string sql)
        {
            DataSet dataset = new DataSet();

            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            adapter.SelectCommand = new MySqlCommand(sql, conn);
            adapter.Fill(dataset);

            return dataset.Tables[0];
        }

        public DataTable GetDataTable(string tablename,string[] columns, string condition_column, object[] condition) {
            string strColumns = "";
            for (int i = 0; i < columns.Length; i++) {
                strColumns += columns[i];
                if (i != columns.Length - 1)
                    strColumns += ",";
            }

            string strCondition = "";
            for (int j = 0; j < condition.Length; j++) {
                strCondition += condition[j].ToString();
                if (j != condition.Length - 1)
                    strCondition += ",";
            }
            string sql = "select " + strColumns + " from " + tablename + " where " + condition_column + " in (" + strCondition + ")";
            return GetDataTable(sql);
        }

        public int ExecuteSql(string sql) {
            MySqlCommand command = new MySqlCommand(sql, conn);
            return command.ExecuteNonQuery();
        }

        public int add_dwg(string schema, uint volume_id, string name, string desc, string stage, DateTime? datetime, sbyte? type, byte[] file)
        {

            string sql = "insert into " + schema + ".dwg values(default,@volume_id,@name,@desc,@stage,@datetime,@type,@file)";

            MySqlCommand command = new MySqlCommand(sql, conn);
            command.CommandType = CommandType.Text;
            command.Parameters.Add("@volume_id", MySql.Data.MySqlClient.MySqlDbType.UInt32);
            command.Parameters.Add("@name", MySql.Data.MySqlClient.MySqlDbType.VarChar);
            command.Parameters.Add("@desc", MySql.Data.MySqlClient.MySqlDbType.String);
            command.Parameters.Add("@stage", MySql.Data.MySqlClient.MySqlDbType.String);
            command.Parameters.Add("@datetime", MySql.Data.MySqlClient.MySqlDbType.DateTime);
            command.Parameters.Add("@type", MySql.Data.MySqlClient.MySqlDbType.Byte);
            command.Parameters.Add("@file", MySql.Data.MySqlClient.MySqlDbType.LongBlob);

            command.Parameters[0].Value = volume_id;
            command.Parameters[1].Value = name;
            command.Parameters[2].Value = desc;
            command.Parameters[3].Value = stage;
            command.Parameters[4].Value = datetime;
            command.Parameters[5].Value = type;
            command.Parameters[6].Value = file;
            //Console.WriteLine("sql = " + command.CommandText);

            return command.ExecuteNonQuery();
        }

        public ArrayList getItems(string schema,item_type item_type, int[] id) {
            return getItems(schema,item_type, idNames[(int)item_type], id);        
        }

        public ArrayList getItems(string schema,item_type item_type, string condition_column, int[] condition)
        {
            string[] strCondition = new string[condition.Length];
            for (int i = 0; i < condition.Length; i++)
            {
                strCondition[i] = condition[i].ToString();
            }
            return getItems(schema,item_type, condition_column, strCondition);
        }

        public ArrayList getItems(string schema,item_type item_type, string condition_column, string[] condition)
        {
            ArrayList alItems = new ArrayList();

            string strCondition = "";
            for (int j = 0; j < condition.Length; j++)
            {
                strCondition += condition[j].ToString();
                if (j != condition.Length - 1)
                    strCondition += ",";
            }

            String sql = "select * from "+schema+"." + tableNames[(int)item_type] + " where " + condition_column + " in (" + strCondition + ")";

            DataTable dataTable = GetDataTable(sql);
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {   
                switch ((int)item_type)
                {
                    case 0:
                        room_item item0 = new room_item();
                        item0.room_id = (int)dataTable.Rows[i][0];
                        item0.name = (string)dataTable.Rows[i][1];
                        alItems.Add(item0);
                        break;
                    case 1:
                        cubicle_item item1 = new cubicle_item();
                        item1.cubicle_id = (int)dataTable.Rows[i][0];
                        item1.name = (string)dataTable.Rows[i][1];
                        item1.size = (string)dataTable.Rows[i][2];
                        item1.manufacture = (string)dataTable.Rows[i][3];
                        alItems.Add(item1);
                        break;
                    case 2:
                        bay_item item2 = new bay_item();
                        item2.bay_id = (int)dataTable.Rows[i][0];
                        item2.name = (string)dataTable.Rows[i][1];
                        item2.number = (string)dataTable.Rows[i][2];
                        item2.vlevel = (int)dataTable.Rows[i][3];
                        alItems.Add(item2);
                        break;
                    case 3:
                        device_item item3 = new device_item();
                        item3.device_id = (int)dataTable.Rows[i][0];
                        item3.device_type = (byte)dataTable.Rows[i][1];
                        item3.name = (string)dataTable.Rows[i][2];
                        item3.description = (string)dataTable.Rows[i][3];
                        item3.manufacture = (string)dataTable.Rows[i][4];
                        item3.type = (string)dataTable.Rows[i][5];
                        item3.config_version = (string)dataTable.Rows[i][6];
                        item3.vlevel = (int)dataTable.Rows[i][7];
                        item3.config_version = (string)dataTable.Rows[i][8];
                        item3.category = (string)dataTable.Rows[i][9];
                        item3.content = (byte[])dataTable.Rows[i][10];
                        item3.b_lock = (bool)dataTable.Rows[i][11];
                        alItems.Add(item3);
                        break;
                    case 4:
                        board_item item4 = new board_item();
                        item4.board_id = (int)dataTable.Rows[i][0];
                        item4.device_id = (int)dataTable.Rows[i][1];
                        item4.name = (string)dataTable.Rows[i][2];
                        item4.position = (int)dataTable.Rows[i][3];
                        item4.description = (string)dataTable.Rows[i][4];
                        item4.manufacture = (string)dataTable.Rows[i][5];
                        item4.type = (string)dataTable.Rows[i][6];
                        alItems.Add(item4);
                        break;
                    case 5:
                        port_item item5 = new port_item();
                        item5.port_id = (int)dataTable.Rows[i][0];
                        item5.board_id = (int)dataTable.Rows[i][1];
                        item5.name = (string)dataTable.Rows[i][2];
                        item5.group = (int)dataTable.Rows[i][3];
                        item5.type = (byte)dataTable.Rows[i][4];
                        item5.direction = (byte)dataTable.Rows[i][5];
                        item5.fiber_plug = (byte)dataTable.Rows[i][6];
                        item5.fiber_mode = (byte)dataTable.Rows[i][7];
                        item5.cascade = (bool)dataTable.Rows[i][8];
                        alItems.Add(item5);
                        break;
                    case 6:
                        vterminal_item item6 = new vterminal_item();
                        item6.vterminal_id = (int)dataTable.Rows[i][0];
                        item6.device_id = (int)dataTable.Rows[i][1];
                        item6.type = (byte)dataTable.Rows[i][2];
                        item6.direction = (byte)dataTable.Rows[i][3];
                        item6.vterminal_no = (int)dataTable.Rows[i][4];
                        item6.description = (string)dataTable.Rows[i][5];
                        item6.pro_desc = (string)dataTable.Rows[i][6];
                        item6.ap_name = (byte[])dataTable.Rows[i][7];
                        item6.ld_inst = (string)dataTable.Rows[i][8];
                        item6.ld_inst = (string)dataTable.Rows[i][9];
                        item6.ln_prefix = (string)dataTable.Rows[i][10];
                        item6.ln_class = (string)dataTable.Rows[i][11];
                        item6.ln_inst = (string)dataTable.Rows[i][12];
                        item6.do_name = (string)dataTable.Rows[i][13];
                        item6.da_name = (string)dataTable.Rows[i][14];
                        alItems.Add(item6);
                        break;
                    case 7:
                        vterminal_connection_item item7 = new vterminal_connection_item();
                        item7.connection_id = (int)dataTable.Rows[i][0];
                        item7.txvterminal_id = (int)dataTable.Rows[i][1];
                        item7.rxvterminal_id = (int)dataTable.Rows[i][2];
                        alItems.Add(item7);
                        break;
                    case 8:
                        room_config_item item8 = new room_config_item();
                        item8.config_id = (int)dataTable.Rows[i][0];
                        item8.room_id = (int)dataTable.Rows[i][1];
                        item8.cubicle_id = (int)dataTable.Rows[i][2];
                        alItems.Add(item8);
                        break;
                    case 9:
                        cubicle_config_item item9 = new cubicle_config_item();
                        item9.config_id = (int)dataTable.Rows[i][0];
                        item9.cubicle_id = (int)dataTable.Rows[i][1];
                        item9.device_id = (int)dataTable.Rows[i][2];
                        item9.position = (int)dataTable.Rows[i][3];
                        item9.number = (string)dataTable.Rows[i][3];
                        alItems.Add(item9);
                        break;
                    case 10:
                        bay_config_item item10 = new bay_config_item();
                        item10.config_id = (int)dataTable.Rows[i][0];
                        item10.bay_id = (int)dataTable.Rows[i][1];
                        item10.device_id = (int)dataTable.Rows[i][2];
                        alItems.Add(item10);
                        break;
                    case 11:
                        port_connection_item item11 = new port_connection_item();
                        item11.connection_id = (int)dataTable.Rows[i][0];
                        item11.infoset_id = (int)dataTable.Rows[i][1];
                        item11.txport_id = (int)dataTable.Rows[i][2];
                        item11.rxport_id = (int)dataTable.Rows[i][1];
                        item11.cable_type = (byte)dataTable.Rows[i][2];
                        alItems.Add(item11);
                        break;
                    case 12:
                        infoset_item item12 = new infoset_item();
                        item12.infoset_id = (int)dataTable.Rows[i][0];
                        item12.name = (string)dataTable.Rows[i][1];
                        item12.description = (string)dataTable.Rows[i][2];
                        item12.type = (byte)dataTable.Rows[i][3];
                        item12.txied_id = (int)dataTable.Rows[i][4];
                        item12.txport_id = (int)dataTable.Rows[i][5];
                        item12.rxied_id = (int)dataTable.Rows[i][6];
                        item12.rxport_id = (int)dataTable.Rows[i][7];
                        item12.switch1_id = (int)dataTable.Rows[i][8];
                        item12.switch2_id = (int)dataTable.Rows[i][9];
                        item12.switch3_id = (int)dataTable.Rows[i][10];
                        item12.txodf_id = (int)dataTable.Rows[i][11];
                        item12.rxodf_id = (int)dataTable.Rows[i][12];
                        alItems.Add(item12);
                        break;
                    case 13:
                        infoset_folder_item item13 = new infoset_folder_item();
                        item13.folder_id = (int)dataTable.Rows[i][0];
                        item13.name = (string)dataTable.Rows[i][1];
                        item13.infoset_id = (int)dataTable.Rows[i][2];
                        alItems.Add(item13);
                        break;
                    case 14:
                        cable_item item14 = new cable_item();
                        item14.cable_id = (int)dataTable.Rows[i][0];
                        item14.cubicle1_id = (int)dataTable.Rows[i][1];
                        item14.cubicle2_id = (int)dataTable.Rows[i][2];
                        item14.name = (string)dataTable.Rows[i][3];
                        item14.cable_type = (byte)dataTable.Rows[i][4];
                        item14.description = (string)dataTable.Rows[i][5];
                        item14.type = (string)dataTable.Rows[i][6];
                        item14.length = (double)dataTable.Rows[i][7];
                        alItems.Add(item14);
                        break;
                    case 15:
                        cable_config_item item15 = new cable_config_item();
                        item15.config_id = (int)dataTable.Rows[i][0];
                        item15.cable_id = (int)dataTable.Rows[i][1];
                        item15.connection_id = (int)dataTable.Rows[i][2];
                        item15.index = (int)dataTable.Rows[i][3];
                        item15.color = (string)dataTable.Rows[i][4];
                        item15.pipe_color = (string)dataTable.Rows[i][5];
                        item15.b_lock = (bool)dataTable.Rows[i][6];
                        alItems.Add(item15);
                        break;
                    case 16:
                        vlan_item item16 = new vlan_item();
                        item16.vlan_id = (int)dataTable.Rows[i][0];
                        item16.name = (string)dataTable.Rows[i][1];
                        item16.description = (string)dataTable.Rows[i][2];
                        alItems.Add(item16);
                        break;
                    case 17:
                        vlan_config_item item17 = new vlan_config_item();
                        item17.config_id = (int)dataTable.Rows[i][0];
                        item17.port_id = (int)dataTable.Rows[i][1];
                        alItems.Add(item17);
                        break;
                    case 18:
                        volume_item item18 = new volume_item();
                        item18.volume_id = (int)dataTable.Rows[i][0];
                        item18.name = (string)dataTable.Rows[i][1];
                        item18.description = (string)dataTable.Rows[i][2];
                        alItems.Add(item18);
                        break;
                    case 19:
                        dwg_item item19 = new dwg_item();
                        item19.dwg_id = (uint)dataTable.Rows[i][0];
                        item19.volume_id = (uint)dataTable.Rows[i][1];
                        item19.name = (string)dataTable.Rows[i][2];
                        item19.description = GetString(dataTable.Rows[i][3]);
                        item19.stage = GetString(dataTable.Rows[i][4]);
                        item19.datetime = GetDateTime(dataTable.Rows[i][5]);
                        item19.type = GetSByte(dataTable.Rows[i][6]);
                        item19.file = (byte[])dataTable.Rows[i][7];
                        alItems.Add(item19);
                        break;
                }
            }


            return alItems;
                
        }

       
    }
}
